﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.IO;
using System.Data.OleDb;//Excel导入导出引用的类
using Model;
using DAL;
namespace Execl.Controllers
{
    public class HomeController : Controller
    {
        /// <summary>
        /// 初始化页面
        /// </summary>
        /// <returns></returns>
        /// 实例化dal
        StudentDal dal = new StudentDal();
        /// <summary>
        /// 显示页面
        /// </summary>
        /// <returns></returns>
        public ActionResult Index()
        {
            List<Student> list = dal.Getshow();
            return View(list);
        }
        /// <summary>
        /// 查到Excel中的数据然后添加到数据库表中  导入
        /// </summary>
        /// <param name="file">上传文件</param>
        /// <returns></returns>
        public ActionResult SC(HttpPostedFileBase file)
        {
           
            //获取路径
            string fileName = Server.MapPath("/Models/"+DateTime.Now.ToString("yyyyMMddhhmm")+Path.GetExtension(file.FileName));
            //获取Execl的路径
            string Execlstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
            file.SaveAs(fileName); 
            //连接Excel的路径
            OleDbConnection conn = new OleDbConnection(Execlstr);
            conn.Open();//打开连接
            //数据适配器获取查询的数据
            OleDbDataAdapter dap = new OleDbDataAdapter("select * from [Sheet1$]",conn);
            //创建数据集对象并存放进去
            DataSet ds = new DataSet();
            dap.Fill(ds);//存放数据到数据集对象中
            //遍历查出数据集中Execl的数据
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                //实例化一个类对象
                Student mod = new Student();
                mod.Id = Convert.ToInt32(item[0]);
                mod.Name = item[1].ToString();
                mod.Sex = item[2].ToString();
                mod.Age = Convert.ToInt32(item[3]);
                //调用添加方法把读到的Execl中的数据添加到数据库中
                dal.Add(mod);
            }
            List<Student> list = dal.Getshow();
            return View("Index",list);
        }
        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        public ActionResult DaoChu()
        {
            List<Student> list = dal.Getshow();
            //设置导出后的Execl的路径及名称
            string Execlstr = "Provider=Microsoft.ace.OLEDB.12.0;Data Source='D:/a.xls';Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(Execlstr);
            conn.Open();
            //导出后新建的Excel中
            string sql = "create table [Sheet1]([编号]INT,[姓名]Text,[性别]Text,[年龄]INT)";
            OleDbCommand cmd = new OleDbCommand(sql,conn);
            cmd.ExecuteNonQuery();
            //循环添加到导出后的Excel中
            foreach (Student item in list)
            {
                string sqls = "insert into [Sheet1$] values('"+item.Id+"','"+item.Name+"','"+item.Sex+"','"+item.Age+"')";
                cmd = new OleDbCommand(sqls,conn);
                cmd.ExecuteNonQuery();
            }
            conn.Close();

            return View();
        }
    }
}